Global Temporary Table
- Table definition is created by a User and the table definition is permanent in DD.
- Space comes from the User's TEMP Space.
- When User logs off the session the data is deleted, but the table definition remain.
- Many Users can populate the same Global table, but each has their own copy.
- Index can created on GTT.
- compression on column , DEFAULT, TITLE clause,Permanent journaling, Identity column are supported by GTT.
- we can collect stats on GTT.
- In a single session 2000 Global temporary table can be materialized
Volatile Table
- Created by the User the table definition is not stored in DD.
- Space comes from the User's Spool space.
- Table and Data are deleted only after a User Logs off the session.
- COMPRESS column, DEFAULT,TITLE clause, RI constraints, Check constraints, Permanent journaling, Named indexes, Identity Column are not supported by Volatile Table.
- No collect stats for VTT
- In a single session 1000 volatile table can be materialized
ON COMMIT PRESERVE/DELETE ROWS
It
is very important option and most of the time you will be using
PRESERVE option. If PRESERVE option is kept then Global Temporary Table
and Volatile Table will store the data after each transaction however
if the DELETE option is kept then it will lose the data once the
transaction is completed
Derived Tables
- Exists only within a query(local to the query).
- Materialized by a SELECT Statement inside a query.
- Space comes from the User's Spool space.
- Deleted when the query ends.
- A derived table can be joined to other tables/views.
- A derived table may be INNER and OUTER JOINed as well.
- If necessary, multiple derived tables can be joined together.
No comments:
Post a Comment